"""
Case Type   : Compatibility
Case Name   : 包裹符前后含其他字符,部分测试点合理报错
Create At   : 2023/06/20
Owner       : c00603284
Description :
        1.建表
        2.包裹符前含其他字符
        3.包裹符后含其他字符
        4.有起始包裹符，无结束包裹符
        5.无起始包裹符，有结束包裹符
        6.包裹符内含转义符,包裹符与转义符不同
Expect      :
        1.创建成功
        2.成功
        3.成功
        4.合理报错
        5.成功
        6.成功
History     :
    2023/8/22 n@ningyali load data语法相关issue修复后，预期结果与实际结果不一致，
    经与开发沟通及与mysql结果对比，修改预期
    2024/9/9 @ningyali 16进制默认显示方式变更，回显变更，优化用例
"""
import os
import unittest

from yat.test import Node

from testcase.COMPATIBILITY.GRAMMAR.LOAD. \
    Opengauss_Compatibility_Grammar_Load_Common import LoadData
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class LoadData0042(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.common = Common()
        self.load_data = LoadData()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.case_lable = 'load_data_0042'
        self.db_name = f'db_{self.case_lable}'
        self.t_name = f't_{self.case_lable}'
        self.file_path = os.path.join('/home', self.pri_user.ssh_user)

        text1 = '----pre_step1：创建数据文件；expect:创建成功----'
        self.log.info(text1)
        self.data1 = "测试aaa,dfs'ac*ac*ac';测试bbb,1231'bc&bc&bc';" \
                     "测试ccc,发发发'c,c,c,cc';测试ddd,'dcdcdc';"
        self.data2 = "测试aaa,'ac*ac*ac'第三方;测试bbb,'bc&bc&bc'feraf;" \
                     "测试ccc,'c,c,c,c,c,c'123;测试ddd,dcdcdc;"
        self.data3 = "测试aaa,'ac*ac*ac';测试bbb,'bc&bc&bc;" \
                     "测试ccc,'c,c,c,c,c,c;测试ddd,dcdcdc;"
        self.data4 = "测试aaa,'ac*ac*ac';测试bbb,bc&bc&bc';" \
                     "测试ccc,c,c,c,c,c,c';测试ddd,dcdcdc;"
        self.data5 = "测试aaa,'ac*ac*ac';测试bbb,'bc*bc*bc';" \
                     "测试ccc,'c*c*c*cc,c';测试ddd,dc*dc****dc;"
        res = self.load_data.create_data_file(self.pri_user, self.case_lable,
                                              self.data1, self.data2,
                                              self.data3, self.data4,
                                              self.data5)
        self.assertTrue(res, '执行失败:' + text1)

        text2 = '----pre_step2：创建兼容B库；expect:创建成功----'
        self.log.info(text2)
        res = self.load_data.create_b_db(self.pri_sh, self.db_name)
        self.assertTrue(res, '执行失败:' + text2)

    def test_load(self):
        text = '----step1：建表；expect：建表成功----'
        self.log.info(text)
        cols = 'c_01 text,c_02 varchar'
        res = self.load_data.create_table(self.pri_sh, self.db_name,
                                          self.t_name, cols)
        self.assertTrue(res, '执行失败:' + text)

        text = "----step2：包裹符前含其他字符；expect：成功----"
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_1.txt')
        res1 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path,
                                        alter='set dolphin.sql_mode='
                                              'ansi_quotes;',
                                        truncate=False, into_type='',
                                        line_start='测试')
        rows, _ = self.common.parse_sql_query_res(res1)
        expect = [['aaa', "dfs'ac*ac*ac'"], ['bbb', "1231'bc&bc&bc'"],
                  ['ccc', "发发发'c"], ['ddd', 'dcdcdc']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = "----step3：包裹符后含其他字符；expect：成功----"
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_2.txt')
        res2 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path,
                                        alter='set dolphin.sql_mode='
                                              'ansi_quotes;',
                                        into_type='',
                                        line_start='测试')
        rows, _ = self.common.parse_sql_query_res(res2)
        expect = [['aaa',
                   "'ac*ac*ac'第三方;测试bbb,'bc&bc&bc'feraf;"
                   "测试ccc,'c,c,c,c,c,c'123;测试ddd,dcdcdc;+"],
                  ['', '']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = "----step4：有起始包裹符，无结束包裹符；expect：成功----"
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_3.txt')
        res3 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path,
                                        alter='set dolphin.sql_mode='
                                              'ansi_quotes;',
                                        into_type='',
                                        line_start='测试')
        rows, _ = self.common.parse_sql_query_res(res3)
        expect = [['aaa', 'ac*ac*ac'],
                  ['bbb', "'bc&bc&bc;测试ccc,'c,c,c,c,c,c;测试ddd,dcdcdc;+"],
                  ['', '']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = "----step5：无起始包裹符，有结束包裹符；expect：成功----"
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_4.txt')
        res4 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path,
                                        alter='set dolphin.sql_mode='
                                              'ansi_quotes;',
                                        into_type='',
                                        line_start='测试')
        rows, _ = self.common.parse_sql_query_res(res4)
        expect = [['aaa', 'ac*ac*ac'], ['bbb', "bc&bc&bc'"], ['ccc', 'c'],
                  ['ddd', 'dcdcdc']]
        self.assertEqual(rows, expect, '执行失败' + text)

        text = "----step6：包裹符内含转义符,包裹符与转义符不同；expect：成功----"
        self.log.info(text)
        f_path = os.path.join(self.file_path, f'{self.case_lable}_5.txt')
        res5 = self.load_data.load_data(self.pri_sh, self.db_name,
                                        self.t_name, f_path,
                                        into_type='', escaped_by='*',
                                        line_start='测试')
        rows, _ = self.common.parse_sql_query_res(res5)
        expect = [['aaa', 'acacac'], ['bbb', 'bc'],
                  ['ccc', 'ccccc,c'], ['ddd', 'dcdc**dc']]
        self.assertEqual(rows, expect, '执行失败' + text)

    def tearDown(self):
        self.log.info('----step7：清理环境；expect：清理成功----')
        res = self.load_data.clear_env(self.pri_sh, self.pri_user,
                                       self.db_name,
                                       os.path.join(self.file_path,
                                                    f"{self.case_lable}*"))
        self.assertTrue(res, '执行失败:清理环境')
        self.log.info(f'----{os.path.basename(__file__)} end----')
